"""
Case Type   : 基础功能
Case Name   : 频繁使gpc失效
Create At   : 2021/05/31
Owner       : songjing20
Description :
    1.开启GPC
    2.重启数据库
    3.创建表
    4.创建prepare语句
    5.调用p1_1
    6.查询计划
    7.alter patririon
    8.查询计划
    9.重复执行20次
Expect      :
    1.开启GPC成功
    2.重启数据库成功
    3.创建表成功
    4.成功
    5.成功
    6.有计划
    7.成功
    8.无计划
    9.重复执行成功
History     :
    Modified by songjing20 2024/7/8: 步骤9重复执行1000次改为20次，teardown中打印执行结果并添加断言
"""
import unittest
from testcase.utils.Logger import Logger
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant


class Gpcclass(unittest.TestCase):

    def setUp(self):
        self.log = Logger()
        self.log.info("-----------this is setup-----------")
        self.log.info("Opengauss_Function_GPC_case0008 start")
        self.constant = Constant()
        self.commonshpri = CommonSH('PrimaryDbUser')
        self.tb_name = "gpc_tb"
        self.pre_name = "gpc_tb_pre"

        result = self.commonshpri.execut_db_sql('show enable_thread_pool;')
        self.log.info(f"enable_thread_pool is {result}")
        self.enable_thread_pool = result.strip().splitlines()[-2]

        result = self.commonshpri.execut_db_sql(
            'show enable_global_plancache;')
        self.log.info(f"enable_global_plancache is {result}")
        self.enable_global_plancache = result.strip().splitlines()[-2]

    def test_index(self):
        self.log.info('-------------1.开启GPC-----------')
        result = self.commonshpri.execute_gsguc(
            "set", self.constant.GSGUC_SUCCESS_MSG, "enable_thread_pool = on")
        self.assertTrue(result)
        result = self.commonshpri.execute_gsguc(
            "set", self.constant.GSGUC_SUCCESS_MSG,
            "enable_global_plancache = on")
        self.assertTrue(result)

        self.log.info('--------------2.重启数据库------------------')
        result = self.commonshpri.restart_db_cluster()
        self.assertTrue(result)

        self.log.info("---------------3.创建表----------------------")
        cmd = f"drop table if exists {self.tb_name};" \
            f"create table {self.tb_name}(i int) " \
            f"partition by range(i) " \
            f"(partition p1 values less than (100)," \
            f"partition p2 values less than (1000));"
        result = self.commonshpri.execut_db_sql(cmd)
        self.log.info(result)
        self.assertIn(self.constant.CREATE_TABLE_SUCCESS, result)

        for i in range(1, 21):
            self.log.info(f"======round {i}=========")
            self.log.info("----------4.创建prepare语句5.执行prepare----------")
            cmd = f"prepare {self.pre_name} as " \
                f"select * from {self.tb_name};" \
                f"execute {self.pre_name};"
            result = self.commonshpri.execut_db_sql(cmd)
            self.log.info(result)
            self.assertIn(self.constant.PREPARE_SUCCESS_MSG, result)
            self.assertIn('0 rows', result)

            self.log.info("-----------6.查询计划:有------------------")
            cmd = f"select * from dbe_perf.global_plancache_status " \
                f"where query='prepare {self.pre_name} " \
                f"as select * from {self.tb_name};' " \
                f"and valid = 't';"
            result = self.commonshpri.execut_db_sql(cmd)
            self.log.info(result)
            self.assertIn('(1 row)', result)

            self.log.info("-----------7.alter------------------")
            cmd = f"ALTER TABLE {self.tb_name} DROP PARTITION p2;"
            result = self.commonshpri.execut_db_sql(cmd)
            self.log.info(result)
            self.assertIn(self.constant.ALTER_TABLE_MSG, result)

            self.log.info("-----------8.查询计划:无------------------")
            cmd = f"select * from dbe_perf.global_plancache_status "
            result = self.commonshpri.execut_db_sql(cmd)
            self.log.info(result)
            self.assertIn('(0 rows)', result)

            self.log.info("-----------9.alter------------------")
            cmd = f"ALTER TABLE {self.tb_name} " \
                f"add PARTITION p2 values less than (1000);"
            result = self.commonshpri.execut_db_sql(cmd)
            self.log.info(result)
            self.assertIn(self.constant.ALTER_TABLE_MSG, result)

    def tearDown(self):
        self.log.info('------------this is tearDown-------------')
        self.log.info('------------------清理环境-------------')
        cmd = f"drop table if exists {self.tb_name} cascade;"
        result = self.commonshpri.execut_db_sql(cmd)
        self.log.info(result)
        res1 = self.commonshpri.execute_gsguc(
            "set", self.constant.GSGUC_SUCCESS_MSG,
            f"enable_thread_pool = {self.enable_thread_pool}")
        self.log.info(res1)
        res2 = self.commonshpri.execute_gsguc(
            "set", self.constant.GSGUC_SUCCESS_MSG,
            f"enable_global_plancache = {self.enable_global_plancache}")
        self.log.info(res2)
        restart_res = self.commonshpri.restart_db_cluster()
        self.log.info(restart_res)
        self.assertIn(self.constant.DROP_TABLE_SUCCESS, result, f"删除表失败")
        self.assertTrue(res1, f"恢复参数enable_thread_pool失败")
        self.assertTrue(res2, f"恢复参数enable_global_plancache失败")
        self.assertTrue(restart_res, f"重启数据库失败")
        self.log.info("-Opengauss_Function_GPC_case0008 end-")
